Java Database Programming with JDBC Java Database Programming with JDBC
by Pratik Patel
Coriolis, The Coriolis Group
ISBN: 1576100561   Pub Date: 10/01/96
  

Previous Table of Contents Next


Chapter 8
The IconStore Multimedia JDBC Application

In the previous chapter, we learned how to process query results with JDBC. In this chapter, we’ll take these query results and put them to use in a multimedia application. The application we’ll be developing, IconStore, will connect to a database, query for image data stored in database tables, and display the images on a canvas. It’s all very simple, and it puts the JDBC to good use by building a dynamic application totally driven by data stored in tables.

IconStore Requirements

The IconStore application will utilize two database tables: ICONCATEGORY and ICONSTORE. The ICONCATEGORY table contains information about image categories, which can be items like printers, sports, and tools. The ICONSTORE table contains information about each image. Tables 8.1 and 8.2 show the database tables’ underlying data structures.

Note that the CATEGORY column in the ICONSTORE is a foreign key into the ICONCATEGORY table. If the category ID for sports is “1”, you can obtain a result set containing all of the sports images by using this statement:

SELECT ID, DESCRIPTION, ICON FROM ICONSTORE WHERE CATEGORY = 1
Table 8.1 The ICONCATEGORY table.
Column Name SQL Type Description
CATEGORY INTEGER Category ID
DESCRIPTION VARCHAR Description of the image category

Table 8.2 The ICONSTORE table.
Column Name SQL Type Description
ID INTEGER Image ID
DESCRIPTION VARCHAR Description of the image
CATEGORY INTEGER Category ID
ICON VARBINARY Binary image

Now, let’s take a look at what’s going on in the application:

  An Icons menu, which is dynamically created by the ICONCATEGORY table, contains each of the image categories as an option. The user can select an image category from this menu to display the proper list of image descriptions in a list box. The ICONSTORE table is used to dynamically build the list.
  The user can select an image description from the list box to display the corresponding image.
  Once an image has been displayed, the user can select the Save As menu option to save the image to disk.

As you can see, IconStore will not be too complicated, but it will serve as a very good foundation for developing database-driven applications.

Building The Database

Now that we’ve established the application’s requirements, we need to build the underlying database. We’ll look at a simple JDBC application to accomplish this, although it may be created by any number of methods. Listing 8.1 shows the BuildDB.java source code. This application uses the SimpleText JDBC driver (covered in great detail in Chapter 10) to create the ICONCATEGORY and ICONSTORE tables, but any JDBC driver can be used in its place.

Listing 8.1 Building the IconStore database.

import java.sql.*;
import java.io.*;

class BuildDB {
//————————————————————————————————————
// main
//————————————————————————————————————
public static void main(String args[]) {
    try {
        // Create an instance of the driver
        java.sql.Driver d = (java.sql.Driver) Class.forName (
                    "jdbc.SimpleText.SimpleTextDriver").newInstance();

        // Properties for the driver
        java.util.Properties prop = new java.util.Properties();

      // URL to use to connect
        String url = "jdbc:SimpleText";

        // The only property supported by the SimpleText driver
        // is "Directory."
        prop.put("Directory", "/java/IconStore");

        // Connect to the SimpleText driver
        Connection con = DriverManager.getConnection(url, prop);

        // Create the category table
        buildCategory(con, "IconCategory");

        // Create the IconStore table
        buildIconStore(con, "IconStore");

        // Close the connection
        con.close();
    }
    catch (SQLException ex) {
        System.out.println("\n*** SQLException caught ***\n");
        while (ex != null) {
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("Message:  " + ex.getMessage());
            System.out.println("Vendor:   " + ex.getErrorCode());
            ex = ex.getNextException ();
        }
        System.out.println("");
    }
    catch (java.lang.Exception ex) {
        ex.printStackTrace ();
    }
}
//————————————————————————————————————
// BuildCategory
// Given a connection object and a table name, create the IconStore
// category database table.
//————————————————————————————————————
protected static void buildCategory(
    Connection con,
    String table)
    throws SQLException
{
    System.out.println("Creating " + table);
    Statement stmt = con.createStatement();
    // Create the SQL statement
    String sql = "create table " + table +
            " (CATEGORY NUMBER, DESCRIPTION VARCHAR)";

    // Create the table
    stmt.executeUpdate(sql);

    // Create some data using the statement
    stmt.executeUpdate("INSERT INTO " + table + " VALUES (1,
      'Printers')");
    stmt.executeUpdate("INSERT INTO " + table + " VALUES (2, 'Sports')");
    stmt.executeUpdate("INSERT INTO " + table + " VALUES (3, 'Tools')");
}
//————————————————————————————————————
// BuildIconStore
// Given a connection object and a table name, create the IconStore
// icon database table.
//————————————————————————————————————
protected static void buildIconStore(
    Connection con,
    String table)
    throws SQLException
{

    System.out.println("Creating " + table);

    Statement stmt = con.createStatement();

    // Create the SQL statement
    String sql = "create table " + table +
            " (ID NUMBER, DESCRIPTION VARCHAR, CATEGORY NUMBER, ICON
              BINARY)";

    // Create the table
    stmt.executeUpdate(sql);
    stmt.close();

    // Create some data using a prepared statement
    sql = "insert into " + table + " values(?,?,?,?)";
    FileInputStream file;
    PreparedStatement ps = con.prepareStatement(sql);

    int category;
    int id = 1;

    // Add the printer icons
    category = 1;

    addIconRecord(ps, id++, "Printer 1", category, "printers/print.gif");
    addIconRecord(ps, id++, "Printer 2", category, "printers/print0.gif");

    // Add the sports icons
    category = 2;

    addIconRecord(ps, id++, "Archery", category, "sports/
      sport_archery.gif");
    addIconRecord(ps, id++, "Baseball", category, "sports/
      sport_baseball.gif");

    // Add the tools
    category = 3;

    addIconRecord(ps, id++, "Toolbox 1", category, "tools/toolbox.gif");
    addIconRecord(ps, id++, "Toolbox 2", category, "tools/toolbox1.gif");
    ps.close();
}

//——————————————————————————————————
// AddIconRecord
// Helper method to add an IconStore record. A PreparedStatement is
// provided to which this method binds input parameters. Returns
// true if the record was added.
//——————————————————————————————————
protected static boolean addIconRecord(
    PreparedStatement ps,
    int id,
    String desc,
    int category,
    String filename)
    throws SQLException
{
    // Create a file object for the icon
    File file = new File(filename);
    if (!file.exists()) {
        return false;
    }

    // Get the length of the file. This will be used when binding
    // the InputStream to the PreparedStatement.
    int len = (int) file.length();

    FileInputStream inputStream;

    try {

        // Attempt to create an InputStream from the File object
        inputStream = new FileInputStream (filename);
    }
    catch (Exception ex) {

            // Some type of failure. Convert it into a SQLException.
            throw new SQLException (ex.getMessage ());
        }

        // Set the parameters
        ps.setInt(1, id);
        ps.setString(2, desc);
        ps.setInt(3,category);
        ps.setBinaryStream(4, inputStream, len);

        // Now execute
        int rows = ps.executeUpdate();
        return (rows == 0) ? false : true;
    }
}


Previous Table of Contents Next